declare @period as int = {period}

select id, userName, lockedUntil, lastActivity, creationDate,
	(
	select count(eventType)
	from audit
	where 
		audit.eventType = 300 and 
		audit.timestamp between DATEADD(DD, -@period, GetDate()) and GetDate() and
		audit.principalId = ns_sec_Users.id
	) as countOfLogInFor_Week,
	(
	select ns_sec_Roles.name
	from ns_sec_UserRoles
	inner join ns_sec_Roles on ns_sec_UserRoles.roleId = ns_sec_Roles.id
	where 
		ns_sec_UserRoles.userId = ns_sec_Users.id and
		ns_sec_Roles.name = 'block'
	) as roleBlock,
	(
		select count(eventType)
		from audit
		where 
			audit.eventType = 300 and 
			audit.timestamp > '2022-01-01 00:00:00' and
			audit.principalId = ns_sec_Users.id
	) as countOfLogInFor_Year
from ns_sec_Users
where ns_sec_Users.userName != 'Latysheva.AIv' and
	ns_sec_Users.userName != 'Baranov.RAn' and
	ns_sec_Users.userName != 'Grishchenko.AA' and
	ns_sec_Users.userName != 'Dundukova.PM' and
	ns_sec_Users.userName != 'Svetlichnyy.SD' and
	ns_sec_Users.userName != 'RAnBaranov'and 
	ns_sec_Users.userName != 'Borisova.EYu' and
	ns_sec_Users.userName != 'khamadiyarova.ve' and
	ns_sec_Users.userName != 'Penyushin.MD'